** JH added cons1 here **
use inequality_data_70s_correct, clear 

** Recover the original cons5
replace cons5 = cons5+ tot_trans - (gasoline_oil+ n_tot_vflow1)/(4*scale) if ref_year<1970
replace cons5 = cons5+ tot_trans - (d_gas+ n_tot_vflow1)/(4*scale) if ref_year>=1970 & ref_year<1980
replace cons5 = cons5 + tot_trans - (gasmopq+gasmocq+n_tot_vflow1)/scale if ref_year>=1980


set more off

*drop years for which we do not have all observations
drop if ref_year==1979


*ensure summation in Total Vehicle Cons works correctly
foreach var of varlist vehicpq vehiccq cartknpq cartkncq cartkupq cartkucq caropq carocq {
replace `var' = 0 if (`var'==. & ref_year>=1980) 
}

*Generate component totals to create different measures of consumption
* food at home
gen fdhome_tot = .
replace fdhome_tot = fdhome/4 if (ref_year<1970)
replace fdhome_tot = d_fdhome/4 if (ref_year>=1970 & ref_year<=1979) 
replace fdhome_tot = fdhomepq2+fdhomecq2 if (ref_year >= 1980)
*transportation 
gen trans_tot = .
replace trans_tot = (publictransother+automobileops+n_tot_vflow1)/4 if(ref_year<1970) 
replace trans_tot = (d_gas + d_othvehic-d_autofin+d_pubtrans+n_tot_vflow1)/4 if (ref_year>=1970 & ref_year<=1979)
replace trans_tot = (transpq+transcq+n_tot_vflow1-(vehfinpq+vehfincq+vehicpq+vehiccq+cartknpq+cartkncq+cartkupq+cartkucq+caropq+carocq)) if (ref_year>=1980)
*utilities 
gen util_tot = .
replace util_tot = utilites/4 if (ref_year<1970) 
replace util_tot = d_utility/4 if (ref_year>=1970 & ref_year<=1979) 
replace util_tot = utilpq+utilcq if (ref_year>=1980) 
*Adjusted utilities from predictions
*replace util_tot = n_adj_util_floor if (cutenure==4 & _merge_rnt==3)
*housing
gen hous_tot = . 
replace hous_tot = (totrentdwe +hflow)/4 if (ref_year<1970) 
replace hous_tot = (d_rent + hflow)/4 if (ref_year>=1970 & ref_year<=1979) 
replace hous_tot = rendwepq+rendwecq+hflow if (ref_year>=1980) 
*difference in adjusted and reported utilities is utility payment included in rent (forced to be positive) 
*gen diff_rnt = n_adj_util_floor - (utilpq+utilcq) if (cutenure==4 & _merge_rnt==3) 
*replace hous_tot = hous_tot - diff_rnt if (cutenure==4 & _merge_rnt==3 & diff_rnt>0 & diff_rnt!=.) 
 
*Recode cons5 to drop all elements of transportation that are not well-measured
*AND to get rid of public housing from measure
rename cons5 uncorrected_cons
gen double cons5 = .
replace cons5= ((uncorrected_cons*scale)+(n_tot_vflow1/4)+gas_mo-trans_tot)/scale if (ref_year<1980)
replace cons5=((uncorrected_cons*scale)+n_tot_vflow1+gas_mo-trans_tot)/scale if (ref_year>=1980)
replace cons5= ((cons5*scale)-(tflow2-tflow))/scale if (ref_year>=1980) 

*Generate core less food, non-housing core, and core less food and utilities 
gen double cons10 = ((cons5*scale)-fdhome_tot)/scale 
gen double cons11 = ((cons5*scale)-hous_tot)/scale 
gen double cons12 = ((cons10*scale)-util_tot)/scale
gen double cons13 = ((cons5*scale)-gas_mo)/scale
gen double cons14 = ((cons5*scale)-(n_tot_vflow1/4))/scale if (ref_year<1980)
replace cons14 = ((cons5*scale)-n_tot_vflow1)/scale if (ref_year>=1980)

*Redo 1959-1961 price adjustment 
/*
foreach var of varlist cons* {
replace `var' = `var'*1.028 if yr_id==9
replace `var' = `var'*1.0098 if yr_id==0
}
*/
*Generate cpi defalator
preserve 
clear         
import excel "C:\Users\jeehoon\Dropbox\Poverty\price_indices17_JH.xlsx", sheet("dat_file 2017") firstrow 
rename Year ref_year
rename cpiursadj cpi_u_rs_adj_17
keep ref_year cpi_u_rs_adj_17
replace cpi_u_rs_adj_17 = 1/cpi_u_rs_adj_17
tempfile cpi_u_rs_adj_17
save `cpi_u_rs_adj_17'
restore
merge m:1 ref_year using `cpi_u_rs_adj_17'
drop _merge


*Generate real consumption figures
foreach var of varlist cons* {
gen r_`var' = `var'*cpi_u_rs_adj_17
}

label var r_cons5 "Well measured consumption (core)" 
label var r_cons6 "Total Consumption"
label var r_cons3 "Total Consumption Plus HI"
label var r_cons10 "Core less food" 
label var r_cons11 "Non-housing core"
label var r_cons12 "Core less food and utilities"
label var r_cons13 "Core less gas and motor oil"
label var r_cons14 "Core less vehicle"
label var r_cons1 "Expenditure"
*create binary variable for gender
gen male=2-sex_ref
*Gen family type dummies
forvalues x=1/5 {
gen fam`x'=0
replace fam`x'=1 if ftype==`x'
gen fam_male`x'=fam`x'*male
}
*Create region dummies 
forvalues x=1/4 {
gen reg`x' =0
replace reg`x'=1 if region==`x'
replace reg`x'=0 if bls_urbn==2
}
gen urban=2-bls_urbn
*Create race dummies
gen race1=0
replace race1=1 if (ref_race==1 & hispanic==0)
gen race2=0
replace race2=1 if (ref_race==2 & hispanic==0)
gen race3=1-race1-race2
*Gen employment indicators 
gen emp=0 if (incweek1 != .)
replace emp=1 if (incweek1 !=. & incweek1>0)
gen emp2=0 if (incweek2 != .) 
replace emp2=1 if (incweek2 != . & incweek2>0)
gen emp_type=0
replace emp_type=1 if (emp==1 & emp2==1)
replace emp_type=2 if (emp==1 & emp2==0)
replace emp_type=3 if (emp==0 & emp2==1)
replace emp_type=4 if (emp==0 & emp2==0)
*Gen race indicator  
gen race_type=. 
replace race_type=1 if (race1==1)
replace race_type=2 if (race2==1)
replace race_type=3 if (race3==1)
*Gen education indicators and dummies (note, ed_type exists for earlier years, drop before creating new variable)
rename ed_type old_ed_type
gen ed_type=.
replace ed_type = old_ed_type if (ref_year<1970)
replace ed_type=1 if (ref_year>=1972 & ref_year<=1973 & (educ_ref==1 | educ_ref==2 | educ_ref==6))
replace ed_type=2 if (ref_year>=1972 & ref_year<=1973 & educ_ref==3)
replace ed_type=3 if (ref_year>=1972 & ref_year<=1973 & educ_ref==4)
replace ed_type=4 if (ref_year>=1972 & ref_year<=1973 & educ_ref==5)
replace ed_type=1 if (qyear>=801 & qyear<=955 & (educ_ref==1 | educ_ref==2 | educ_ref==7))
replace ed_type=2 if (qyear>=801 & qyear<=955 & educ_ref==3)
replace ed_type=3 if (qyear>=801 & qyear<=955 & educ_ref==4)
replace ed_type=4 if (qyear>=801 & qyear<=955 & (educ_ref==5 | educ_ref==6))
replace ed_type=1 if (qyear>955 & (educ_ref==0 | educ_ref==10 | educ_ref==11))
replace ed_type=2 if (qyear>955 & educ_ref==12)
replace ed_type=3 if (qyear>955 & (educ_ref>=13 & educ_ref<=14))
replace ed_type=4 if (qyear>955 & (educ_ref>=15 & educ_ref<=17))
forvalues x=1/4 {
gen ed`x'=0
replace ed`x'=1 if ed_type==`x'
}
*Gen age indicators 
gen age_grp=.
replace age_grp=1 if (age_ref>=0 & age_ref<=34)
replace age_grp=2 if (age_ref>=35 & age_ref<=49)
replace age_grp=3 if (age_ref>=50 & age_ref<=64)
replace age_grp=4 if (age_ref>=65)
*Gen home owner dummy
gen ownhome=.
replace ownhome=1 if (cutenure>=1 & cutenure<=3)
replace ownhome=0 if (cutenure>=4 & cutenure<=6)
gen region_code=region
replace region_code=5 if (bls_urbn==2) 
*Gen demographic groups 
gen dem_group2=10*ftype+emp_type
gen dem_group3=10*ftype+race_type
gen dem_group4=10*ftype+region_code
gen dem_group6=100*ftype+10*ed_type+race1
*Change dem_group6 into continuous variable 1/40
egen counter=group(dem_group6)
quietly {
forvalues x=1/40 {
gen grp_dum`x'=0
replace grp_dum`x'=1 if counter==`x'
}
}

*Collapse data on the 10th, 50th, and 90th percentiles, by year
preserve 
collapse (p10) p10cons5=r_cons5 p10cons10=r_cons10 p10cons11=r_cons11 p10cons12=r_cons12 p10cons13=r_cons13 p10cons14=r_cons14 p10cons6=r_cons6 p10cons3=r_cons3 p10cons1=r_cons1 ///
(p50) medcons5=r_cons5 medcons10=r_cons10 medcons11=r_cons11 medcons12=r_cons12 medcons13=r_cons13 medcons14=r_cons14 medcons6=r_cons6 medcons3=r_cons3 medcons1=r_cons1 ///
(p90) p90cons5=r_cons5 p90cons10=r_cons10 p90cons11=r_cons11 p90cons12=r_cons12 p90cons13=r_cons13 p90cons14=r_cons14 p90cons6=r_cons6 p90cons3=r_cons3 p90cons1=r_cons1 [w=wgt20] , by(ref_year)

*generate ratios 
foreach x in "cons5" "cons10" "cons11" "cons12" "cons13" "cons14" "cons6" "cons3" "cons1" { 
gen ratio`x'_90_10 = p90`x'/p10`x'
gen ratio`x'_90_50 = p90`x'/med`x'
gen ratio`x'_50_10 = med`x'/p10`x'
}
*export to excel for cleaning up and presenation 
export excel using cons_inequality_data_adj.xls , firstrow(var) replace 

restore 

* JH added this *
* Consumption inequality by family type 
*Collapse data on the 10th, 50th, and 90th percentiles, by year by group
preserve 
collapse (p10) p10cons5g=r_cons5 (p50) medcons5g=r_cons5 (p90) p90cons5g=r_cons5 [w=wgt20], by(ref_year ftype)
reshape wide p10cons5g medcons5g p90cons5g, i(ref_year) j(ftype) 
*generate ratios 
forvalues y = 1/5{ 
local x cons5g
gen ratio`x'`y'_90_10 = p90`x'`y'/p10`x'`y'
gen ratio`x'`y'_90_50 = p90`x'`y'/med`x'`y'
gen ratio`x'`y'_50_10 = med`x'`y'/p10`x'`y'
}
rename ref_year year
keep year ratio*
order year *90_10 *90_50 *50_10
*export to excel for cleaning up and presenation 
export excel using cons5_inequality_by_group.xls , firstrow(var) replace 

restore 


* JH added this *
* Mean Consumption Level by Quintile (change the ref_year to the most recent year)
*Collapse data on mean, by year 
preserve 
keep if ref_year==1980|ref_year==1988|ref_year==2017
collapse (mean) r_cons5 r_cons10 r_cons3 r_cons6 [w=wgt20] , by(ref_year)
*export to excel for cleaning up and presenation 
save tot_mean, replace
restore 

preserve 
keep if ref_year==1980|ref_year==1988|ref_year==2017
collapse (p5) q1cons5=r_cons5 q1cons10=r_cons10 q1cons3=r_cons3 q1cons6=r_cons6 (p20) q2cons5=r_cons5 q2cons10=r_cons10 q2cons3=r_cons3 q2cons6=r_cons6 ///
(p40) q3cons5=r_cons5 q3cons10=r_cons10 q3cons3=r_cons3 q3cons6=r_cons6 (p60) q4cons5=r_cons5 q4cons10=r_cons10 q4cons3=r_cons3 q4cons6=r_cons6 ///
(p80) q5cons5=r_cons5 q5cons10=r_cons10 q5cons3=r_cons3 q5cons6=r_cons6 (p95) q6cons5=r_cons5 q6cons10=r_cons10 q6cons3=r_cons3 q6cons6=r_cons6, by(ref_year)
save quintile, replace
restore
merge m:1 ref_year using quintile
drop _merge

* 5th-20th, 20-40th, 40-60th, 60-80th, 80-95th percentiles
keep if ref_year==1980|ref_year==1988|ref_year==2017
foreach i in 1 2 3 4 5 {
local j =`i'+1
foreach k in "cons6" {
preserve
collapse (mean) r_cons5 r_cons10 r_cons3 r_cons6 [w=wgt20] if r_`k'>q`i'`k' & r_`k'<=q`j'`k', by(ref_year)
save q`i'`k', replace
restore
}
}
* 0-20th, 80-100th percentiles
foreach k in "cons6" {
preserve
collapse (mean) r_cons5 r_cons10 r_cons3 r_cons6 [w=wgt20] if r_`k'<=q2`k', by(ref_year)
save q6`k', replace
restore
}
foreach k in "cons6" {
preserve
collapse (mean) r_cons5 r_cons10 r_cons3 r_cons6 [w=wgt20] if r_`k'>q5`k', by(ref_year)
save q7`k', replace
restore
}

foreach k in "cons6" {
clear
use tot_mean
foreach i in 1 2 3 4 5 6 7 {
append using q`i'`k'
}
export excel using quintile_sort_on_`k'.xls, firstrow(var) replace
}




**************************************************************************************
**************           Table 8: Cons 5 by asset quintile          ****************** 
**************************************************************************************


use inequality_data_70s_correct, clear 

** Recover the original cons5
replace cons5 = cons5+ tot_trans - (gasoline_oil+ n_tot_vflow1)/(4*scale) if ref_year<1970
replace cons5 = cons5+ tot_trans - (d_gas+ n_tot_vflow1)/(4*scale) if ref_year>=1970 & ref_year<1980
replace cons5 = cons5 + tot_trans - (gasmopq+gasmocq+n_tot_vflow1)/scale if ref_year>=1980


set more off

*drop years for which we do not have all observations
drop if ref_year==1979


*ensure summation in Total Vehicle Cons works correctly
foreach var of varlist vehicpq vehiccq cartknpq cartkncq cartkupq cartkucq caropq carocq {
replace `var' = 0 if (`var'==. & ref_year>=1980) 
}

*Generate component totals to create different measures of consumption
* food at home
gen fdhome_tot = .
replace fdhome_tot = fdhome/4 if (ref_year<1970)
replace fdhome_tot = d_fdhome/4 if (ref_year>=1970 & ref_year<=1979) 
replace fdhome_tot = fdhomepq2+fdhomecq2 if (ref_year >= 1980)
*transportation 
gen trans_tot = .
replace trans_tot = (publictransother+automobileops+n_tot_vflow1)/4 if(ref_year<1970) 
replace trans_tot = (d_gas + d_othvehic-d_autofin+d_pubtrans+n_tot_vflow1)/4 if (ref_year>=1970 & ref_year<=1979)
replace trans_tot = (transpq+transcq+n_tot_vflow1-(vehfinpq+vehfincq+vehicpq+vehiccq+cartknpq+cartkncq+cartkupq+cartkucq+caropq+carocq)) if (ref_year>=1980)
*utilities 
gen util_tot = .
replace util_tot = utilites/4 if (ref_year<1970) 
replace util_tot = d_utility/4 if (ref_year>=1970 & ref_year<=1979) 
replace util_tot = utilpq+utilcq if (ref_year>=1980) 
*Adjusted utilities from predictions
*replace util_tot = n_adj_util_floor if (cutenure==4 & _merge_rnt==3)
*housing
gen hous_tot = . 
replace hous_tot = (totrentdwe +hflow)/4 if (ref_year<1970) 
replace hous_tot = (d_rent + hflow)/4 if (ref_year>=1970 & ref_year<=1979) 
replace hous_tot = rendwepq+rendwecq+hflow if (ref_year>=1980) 
*difference in adjusted and reported utilities is utility payment included in rent (forced to be positive) 
*gen diff_rnt = n_adj_util_floor - (utilpq+utilcq) if (cutenure==4 & _merge_rnt==3) 
*replace hous_tot = hous_tot - diff_rnt if (cutenure==4 & _merge_rnt==3 & diff_rnt>0 & diff_rnt!=.) 
 
*Recode cons5 to drop all elements of transportation that are not well-measured
*AND to get rid of public housing from measure
rename cons5 uncorrected_cons
gen double cons5 = .
replace cons5= ((uncorrected_cons*scale)+(n_tot_vflow1/4)+gas_mo-trans_tot)/scale if (ref_year<1980)
replace cons5=((uncorrected_cons*scale)+n_tot_vflow1+gas_mo-trans_tot)/scale if (ref_year>=1980)
replace cons5= ((cons5*scale)-(tflow2-tflow))/scale if (ref_year>=1980) 

*Generate cpi defalator
preserve 
clear         
import excel "C:\Users\jeehoon\Dropbox\Poverty\price_indices17_JH.xlsx", sheet("dat_file 2017") firstrow 
rename Year ref_year
rename cpiursadj cpi_u_rs_adj_17
keep ref_year cpi_u_rs_adj_17
replace cpi_u_rs_adj_17 = 1/cpi_u_rs_adj_17
tempfile cpi_u_rs_adj_17
save `cpi_u_rs_adj_17'
restore
merge m:1 ref_year using `cpi_u_rs_adj_17'
drop _merge

*Generate real consumption figures
foreach var of varlist cons* {
gen r_`var' = `var'*cpi_u_rs_adj_17
}

label var r_cons5 "Well measured consumption (core)" 

* Mean Well-meausred Consumption Level by Asset Quintile 
*Collapse data on mean, by year 
preserve 
keep if ref_year==1991|ref_year==2000|ref_year==2006|ref_year==2010|ref_year==2017
collapse (mean) r_cons5 [w=wgt20], by(ref_year)
*export to excel for cleaning up and presenation 
save tot_mean, replace
restore 

preserve 
keep if ref_year==1991|ref_year==2000|ref_year==2006|ref_year==2010|ref_year==2017
collapse (min) q1asset=r_tot_ass_incl_heq q1fasset=r_tot_assets (p20) q2asset=r_tot_ass_incl_heq q2fasset=r_tot_assets ///
(p40) q3asset=r_tot_ass_incl_heq q3fasset=r_tot_assets (p60) q4asset=r_tot_ass_incl_heq q4fasset=r_tot_assets ///
(p80) q5asset=r_tot_ass_incl_heq q5fasset=r_tot_assets (max) q6asset=r_tot_ass_incl_heq q6fasset=r_tot_assets, by(ref_year)
save quintile, replace
restore
merge m:1 ref_year using quintile
drop _merge

* 0-20th, 20-40th, 40-60th, 60-80th, 80-100th percentiles
keep if ref_year==1991|ref_year==2000|ref_year==2006|ref_year==2010|ref_year==2017
foreach i in 1 2 3 4 5 {
local j =`i'+1
preserve
collapse (mean) r_cons5 [w=wgt20] if r_tot_ass_incl_heq>q`i'asset & r_tot_ass_incl_heq<=q`j'asset, by(ref_year)
gen q = `i'
save q`i'asset, replace
restore
}


preserve
clear
use tot_mean
gen q = 0
foreach i in 1 2 3 4 5 {
append using q`i'asset
}
reshape wide r_cons5, i(q) j(ref_year)
export excel using quintile_sort_on_asset.xls, firstrow(var) replace 
restore